﻿
CREATE OR REPLACE FUNCTION public.gaincode(
    p_bill character varying,
    p_type integer,
    p_field character varying,
    p_billdate timestamp with time zone,
    p_org_id integer,
    p_userid integer)
  RETURNS character varying AS
$BODY$
declare
    v_numhead character varying;
    v_numfoot integer;
    v_footlenth integer;
    v_code character varying;
    v_cnt integer;
    v_type	character varying;
    v_sql text;
begin  
-----current_date
   select numhead || to_char(p_billdate,numdate) as numhead,numfoot,case when footlenth >8 then 8 else footlenth end
   into v_numhead,v_numfoot,v_footlenth
   from  public.tabletype where code=p_bill  and type=p_type;
   if p_bill='contracts' and p_field !='code' then 
      v_type :='JJHT';
	   select numhead || '-' || (case v_type when 'sheb' then 'sb' when 'gongc' then 'gc' when 'gongcfw' then 'gcfw' when 'houqwz' then 'hqwz'
	   when 'yilwz' then 'ylwz' when 'yaop' then 'yp' when 'xiaodp' then 'xdp' when 'shij' then 'sj' when 'yiyhc' then 'yyhc' 
	   when 'houqfw' then 'hqfw' when 'shenjfw' then 'sjfw' when 'caiwfw' then 'cwfw' when 'chengz' then 'cz' when 'chuz' then 'cz'
	   when 'xuanc' then 'xc' when 'peix' then 'px' when 'gonghcg' then 'ghcg' when 'weix' then 'wx' when 'weib' then 'wb'
	   when 'qit' then 'qt' else v_type end) || '-' || to_char(p_billdate,numdate) || '-' ||
	   COALESCE((select t2.shortname From public.users t1 inner join public.orgunit t2 on t1.orgunit_id=t2.id where t1.id=p_userid),'') || '-' as numhead,
	   numfoot,case when footlenth >8 then 8 else footlenth end
	   into v_numhead,v_numfoot,v_footlenth
	   from  public.tabletype where org_id=p_org_id and  code=p_bill;
   end if;
   v_sql := 'select COALESCE(max(' || p_field || '),''' || v_numhead || ''') from public.' || p_bill || ' where ' || p_field || ' like ''' || upper(v_numhead) || '%'' and org_id=' || p_org_id ;
   execute  v_sql into v_code; 
   if v_code = v_numhead then
      select v_numhead || to_char(v_numfoot,'fm' || substr('00000000',1,v_footlenth)) into v_code ;
   else
      select v_numhead || to_char(to_number(substr(v_code,length(v_numhead)+1), '9999999999999999999')+1,'fm' || substr('00000000',1,v_footlenth)) into v_code ;
   end if;
   RETURN upper(v_code)::character varying; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
